package com.dao.chart;

import com.model.bo.ChartsColumnConfig;
import com.model.bo.ColumnConfigAndSql;
import com.model.po.ChartConfig;
import com.model.po.ChartConfigList;
import com.model.po.ChartsConfigToDash;
import com.model.pojo.TestPage;
import com.model.vo.configVo.GroupInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface ChartsConfigMapper {

    @Insert("insert into bi_charts(id,CHART_NAME,CHART_TYPE,BD_DATA_ID,CHART_CONFIG,GROUP_BY,ACCESS_AUTHORITY,UPDATE_AUTHORITY,BC_CHARTS_GROUP,CHART_DESCRIBES,BC_CHART_STYLE," +
            "create_by,create_date, bc_charts_option, create_id, BC_FETCHCONFIG) " +
            "VALUES (#{chartId},#{chartName},#{chartType}, #{dataId}, #{chartConfig}, #{groupBy}, #{accessAuthority}, #{updateAuthority}, #{chartsGroup}," +
            "#{describes},#{style}, #{createBy},to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'),  #{chartOption}, #{createId}, #{fetchConfig})" )
//    @Options(useGeneratedKeys=false, keyProperty = "chartId",keyColumn = "id")
    @SelectKey(before=true,keyProperty="chartId",resultType=Integer.class,statement="SELECT bi_charts_sequence.nextval from dual",keyColumn = "id")
    void insertCharts(ChartConfig chartConfig);

    @Delete("<script>" +
            "delete from bi_charts where id in " +
            "("+
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}"+
            "</foreach>" +
            ")"+
            "</script>")
    void deleteCharts(List<Integer> idList);

    @Select("select bc.id as chartId, chart_name as chartName, chart_type as chartType, bc.create_by as createBy, bc.create_date as createDate, bc_charts_group as chartsGroup" +
            "            ,bc.bc_filters as filters, bc.BD_DATA_ID as dataId, bc_table_name as tableName, CHART_CONFIG as chartConfig, bc.GROUP_BY as groupBy, bc_charts_option as chartOption, " +
            "            bc.ACCESS_AUTHORITY as accessAuthority, UPDATE_AUTHORITY as updateAuthority, CHART_DESCRIBES as describes, BC_CHART_STYLE as style, BC_FILTERS as filters " +
            "            ,bc.create_id as createId, bd.DATA_NAME as dataName from bi_charts bc left join BI_DATA_CONNECTORS bd on bc.BD_DATA_ID = bd.id" +
            "             where bc.id = #{id}")
    ChartConfig getOneChart(int id);

    @Select("select DATA_NAME as name from BI_DATA_CONNECTORS where id = #{id}")
    String getDataNameById(int id);

    /*
    查看图表列表
     */
    @Select("select id as chartId, chart_name as chartName, chart_type as chartType, create_by as createBy, create_date as createDate" +
            ",bc_filters as filters, CHART_DESCRIBES as describes, bc_charts_group as chartsGroup, bc_charts_option as chartOption from bi_charts")
    List<ChartConfigList> getListCharts();

    /*
    权限查看图表列表
     */
    @Select("  select case " +
            "    when bc.CREATE_ID = #{id} and bc.BD_DATA_ID in (select id from bi_data_connectors where create_id=#{id}) then '1'" +
            "    when bc.CREATE_ID = #{id} and bc.BD_DATA_ID not in (select id from bi_data_connectors where create_id=#{id}) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs1 " +
            "        where bs1.IS_OPEN='1' and bs1.BS_ID in (" +
            "          select bo1.BO_ST_ID from bi_db_object bo1 " +
            "            where bo1.BO_TYPE = '0' and bo1.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=#{id} ) " +
            "            or bo1.bo_type = '1' and bo1.bo_ob_id = #{id}" +
            "        ) and bs1.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) > 0 then '1'" +
            "    when bc.CREATE_ID = #{id} and bc.BD_DATA_ID not in (select id from bi_data_connectors where create_id=#{id}) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs2 " +
            "        where bs2.IS_OPEN='1' and bs2.BS_ID in (" +
            "          select bo2.BO_ST_ID from bi_db_object bo2 " +
            "            where bo2.BO_TYPE = '0' and bo2.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=#{id} ) " +
            "            or bo2.bo_type = '1' and bo2.bo_ob_id = #{id}" +
            "        ) and bs2.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) = 0 then '0' " +
            "    when bc.CREATE_ID != #{id} and bc.BD_DATA_ID in (select bdc1.id " +
            " from bi_data_connectors bdc1 where bdc1.create_id=bc.CREATE_ID) then '1'" +

            "    when bc.CREATE_ID != #{id} and bc.BD_DATA_ID not in (select bdc2.id " +
            "" +
            " from bi_data_connectors bdc2 where bdc2.create_id=bc.CREATE_ID) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs2" +
            "        where bs2.IS_OPEN='1' and bs2.BS_ID in (" +
            "          select bo2.BO_ST_ID from bi_db_object bo2 " +
            "            where bo2.BO_TYPE = '0' and bo2.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=bc.CREATE_ID ) " +
            "            or bo2.bo_type = '1' and bo2.bo_ob_id = bc.CREATE_ID" +
            "        ) and bs2.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) > 0 then '1'" +
            "    when bc.CREATE_ID != #{id} and bc.BD_DATA_ID not in (select bdc3.id " +
            " from bi_data_connectors bdc3 where bdc3.create_id=bc.CREATE_ID) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs2" +
            "        where bs2.IS_OPEN='1' and bs2.BS_ID in (" +
            "          select bo2.BO_ST_ID from bi_db_object bo2 " +
            "            where bo2.BO_TYPE = '0' and bo2.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=bc.CREATE_ID ) " +
            "            or bo2.bo_type = '1' and bo2.bo_ob_id = bc.CREATE_ID" +
            "        ) and bs2.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) = 0 then '0'" +
            "    else null end authority, case when bc.BD_DATA_ID not in(select id from bi_data_connectors) then '1' else '0' end dbStatus, bc.id as chartId, bc.chart_name as chartName, bc.chart_type as chartType, bc.create_by as createBy, bc.create_date as createDate" +
            "    ,bc.bc_filters as filters, bc.CHART_DESCRIBES as describes, bc.bc_charts_group as chartsGroup, bc.bc_charts_option as chartOption ,bc.bd_data_id as dataId,bd.data_name as dataName, bc.create_id as createId  " +
            "     from bi_charts bc left join bi_data_connectors bd on bc.bd_data_id=bd.id where (bc.create_id = #{id} or bc.id in (" +
            "      select bc_chart_id from bi_chart_strategys where BC_ID in (" +
            "        select BO_ST_ID from BI_CHART_OBJECT where (BO_TYPE='1' and BO_OB_ID = #{id})" +
            "        or (" +
            "          BO_TYPE='0' and BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=#{id} )" +
            "        )" +
            "      ) and is_open = '1' " +
            "    ))")
    List<ChartConfigList> getListByUser(@Param("id") int id, TestPage testPage);

    /*
    通过Id查找表名
    */
    @Select("select dc.table_name from bi_data_connectors dc left join bi_charts c on dc.id =c.bd_data_id where c.id=#{chartId}")
    String getTableName(@Param("chartId") int id);


    /*
    通过ID查找数据源配置
     */
    @Select("select dc.db_config from bi_data_connectors dc left join bi_charts c on dc.id =c.bd_data_id where c.id=#{chartId}")
    String getBaseConifg(@Param("chartId") int id);

    /*
    通过ID查找数据源配置
     */
    @Select("select bd_data_id from bi_charts where id=#{chartId}")
    int getBaseId(@Param("chartId") int id);

    /*
    通过ID查找数据库sql
     */
    @Select("select table_name from bi_data_connectors where id = #{id}")
    String getTableNameByBase(@Param("id") int id);

    /*
    更新图表配置
     */
    @Update("<script>" +
            "UPDATE bi_charts set "  +
            "CHART_NAME = #{chartName}" +
            "<if test=\"chartType != null\"> , CHART_TYPE = #{chartType} </if>" +
            "<if test=\"createBy != null\"> , CREATE_BY = #{createBy} </if>" +
            "<if test=\"groupBy != null\"> , GROUP_BY = #{groupBy} </if>" +
            "<if test=\"chartConfig != null\"> , CHART_CONFIG = #{chartConfig} </if>" +
            "<if test=\"describes != null\"> , CHART_DESCRIBES = #{describes} </if>" +
            "<if test=\"filters != null\"> , BC_FILTERS = #{filters} </if>" +
            "<if test=\"accessAuthority != null\"> , ACCESS_AUTHORITY = #{accessAuthority} </if>" +
            "<if test=\"updateAuthority != null\"> , UPDATE_AUTHORITY = #{updateAuthority} </if>" +
            "<if test=\"style != null\"> , BC_CHART_STYLE = #{style} </if>" +
            "<if test=\"chartsGroup !=null\">, BC_CHARTS_GROUP = #{chartsGroup}</if>"+
            "<if test=\"chartOption !=null\">, bc_charts_option = #{chartOption}</if>"+
            "<if test=\"fetchConfig !=null\">, BC_FETCHCONFIG = #{fetchConfig}</if>"+
            "<if test=\"updateDate != null\"> , UPDATE_DATE = to_date(#{updateDate},'YYYY-MM-DD hh24:mi:ss') </if>" +
            "where id = #{chartId}" +
            "</script>")
    void updateChartConfig(ChartConfig chartConfig);

    /*
    移交图表
     */
    @Update("update bi_charts set create_by = #{name}, create_id = #{createId} where id = #{baseId}")
    void changeChartOrder(@Param("name") String name, @Param("createId") int userId, @Param("baseId") int baseId);

    /*
    更新图表分组
     */
    @Update("update bi_charts set BC_CHARTS_GROUP = #{chartsGroup} where id = #{chartId}")
    void updateConfigGroup(@Param("chartId") int chartId, @Param("chartsGroup") int chartsGroup);

    /*
   创建图表分组
    */
    @Insert("insert into bi_charts_group(bc_id, bc_group_name, bc_index, bc_father_id, create_date)" +
            "values(#{id}, #{groupName}, #{groupIndex}, #{fatherId}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT BI_CHARTS_GROUP_SEQUENCE.nextval from dual",keyColumn = "bc_id")
    void setChartsGroup(GroupInfo group);

    /*
    更新图表分组
     */
    @Update("<script>"+
            "UPDATE bi_charts_group set "+
            "bc_group_name = #{groupName}" +
            "<if test=\"groupIndex != null\"> , bc_index = #{groupIndex} </if>" +
            "<if test=\"fatherId != null\"> , bc_father_id = #{fatherId} </if>" +
            "<if test=\"createBy != null\"> , create_by = #{createBy} ,</if>" +
            "updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') "+
            " where bc_id = #{id}" +
            "</script>")
    void updataChartsGroup(GroupInfo groupInfo);

//    /*
//    批量更新
//     */
//    @Update("<script>"+
//            "<foreach item = \"value\" index=\"key\" collenction=\"groupInfoMap.entrySet()\" separator=\",\">"+
//            "update bi_charts_group"+
//            "set"+
//            "bc_group_name = #{#{value}.groupName}" +
//            "<if test=\"value.groupIndex != null\"> , bc_index = #{#{value}.groupIndex} </if>" +
//            "<if test=\"value.fatherId != null\"> , bc_father_id ={#{value}.fatherId} </if>" +
//            "<if test=\"value.createBy != null\"> , create_by = {#{value}.createBy} </if>" +
//            ",updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') "+
//            " where bc_id = #{value.id}" +
//            "</foreach>"+
//            "</script>")
//    void updataListGroup(@Param("groupInfoMap") Map<Integer, GroupInfo> groupInfoMap);

    /*
    删除分组
     */
    @Delete("<script>" +
            "delete from bi_charts_group where bc_id in" +
            "(" +
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}" +
            " </foreach>" +
            ")" +
            "</script>")
    void delChartsGroup(List<Integer> isList);

    /*
    查询分组
     */
    @Select("select bc_id as id, bc_group_name as groupName, bc_index as groupIndex, bc_father_id as fatherId, create_by as createBy," +
            "create_date as createDate from bi_charts_group")
    List<GroupInfo> getChartsGroup();

    /*
    查询分组是否有子分组
     */
    @Select("select BC_GROUP_NAME from bi_charts_group where bc_father_id = #{id}")
    List<String> getChartFatherId(int id);

    /*
    查询是否有数据源正在使用
     */
    @Select("select chart_name from bi_charts where BC_CHARTS_GROUP = #{id}")
    List<String> getChartName(int id);

    /*
    查询图表创建人ID
     */
    @Select("select create_id from bi_charts where id = #{id}")
    int getCreateId(@Param("id") int id);

    /*
    通过图表id查询数据源创建人ID
     */
    @Select("select bd.create_id from bi_charts bc,bi_data_connectors bd where bc.bd_data_id = bd.id and bc.id = #{id}")
    int getCreateIdBychart(int id);

    /*
    通过图表查询数据源ID
     */
    @Select("select bd_data_id from bi_charts where id = #{id}")
    int getBaseIdByChart(int id);

    /*
    通过用户Id查询数据源ID
     */
    @Select("select bs_db_id from bi_db_strategys where bs_id in (select bo_st_id from " +
            "bi_db_object where (bo_ob_id = #{id} and bo_type = '1') or (bo_ob_id = '1' and bo_type = '0' and bo_ob_id in" +
            "(select br_user_group from bi_user_rel_groups where br_user_id= #{id}))) and is_open = '1'")
    int getBaseIdByUser(int id);

    @Select("select table_name as sql, columns_config as columnConfig from bi_data_connectors where id = #{id}")
    ColumnConfigAndSql getConfigAndSql(int id);

    /*
    图表ID查询列配置
     */
    @Select("select columns_config from bi_data_connectors bc, bi_charts bch where bc.id = bch.bd_data_id and bch.id = #{id} ")
    String getConfigByChartId(int id);

    /*
    查询图表数据列信息
     */
    @Select("${sql}")
    List<Map<String, Object>> getChartsData(@Param("sql") String sql, TestPage testPage);

    /*
    通过图表ID获取图表配置
     */
    @Select("select id, chart_type as chartType, BC_FETCHCONFIG as fetchConfig from bi_charts where id = #{id}")
    ChartsConfigToDash getChartConfigToDash(int id);

    /*
    通过图表ID查询图表列配置和style
     */
    @Select("select bc.id as id, bc.chart_config as chartConfig, bc.BC_CHART_STYLE as chartStyle,bc.id, bc.CHART_NAME as chartName," +
            "bc.chart_type as chartType, bc.BD_DATA_ID as dataId, bc.create_id as createId, bc.CREATE_BY as createBy," +
            "bd.data_name as dataName " +
            "from bi_charts bc left join bi_data_connectors bd on bc.bd_data_id = bd.id where bc.id = #{id}")
    ChartsColumnConfig getChartsColumn(int id);

}
